package com.jomoo.account.dao.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.jomoo.account.dao.AccountdDao;
import com.jomoo.common.pojo.JomooAccount;
import com.jomoo.common.utils.Constants;

/**
 * 
* @ClassName: AccountDaoImpl 
* @Description:  
* @author sddm.haosc
* @date 2017年12月12日 上午10:24:28 
*
 */
@Repository
@Transactional(rollbackFor = Exception.class)
public class AccountDaoImpl implements AccountdDao
{
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;

	/**
	 * 持久化任意对象
	 */
	@Override
	public Integer saveAllObj(Object obj) throws Exception
	{
		Integer iId = null;
		Session session = sessionFactory.openSession();

		try
		{
			iId = (Integer) session.save(obj);
		}
		catch (Exception e)
		{
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return iId;
	}

	/**
	 * 账单信息查询
	 * 
	 * @param iType
	 * @param iState
	 * @param fstartNum
	 * @param fendNum
	 * @param strBeginTime
	 * @param strEndTime
	 * @return
	 * @throws Exception
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<JomooAccount> getAccounts(Integer iType, Integer iState, Float fstartNum,
			Float fendNum, Date strBeginTime, Date strEndTime)
			throws Exception
	{
		Session session = sessionFactory.openSession();

		List<JomooAccount> caccountList = null;

		try
		{
			StringBuilder hql = new StringBuilder();
			
			hql.append(" select new JomooAccount(ja.id, js.statusName, ja.accountSale, "
					+ " ja.accountTime, ja.remark, ja.isBalance) "
					+ " from JomooAccount ja, JoMooStatus js "
					+ " where ja.active=1 and js.active=1"
					+ " and ja.accountType=js.statusValue"
					+ " and js.stateName =:stateName ");

			if (StringUtils.isNotBlank(iType.toString()))
			{
				hql.append(" and ja.accountType=:type ");
			}

			if (StringUtils.isNotBlank(iState.toString()))
			{
				hql.append(" and ja.isBalance=:state ");
			}

			if (StringUtils.isNotBlank(fstartNum.toString()))
			{
				hql.append(" and ja.accountSale>=:startnum ");
			}

			if (StringUtils.isNotBlank(fendNum.toString()))
			{
				hql.append(" and ja.accountSale<=:endnum ");
			}

			if (null != strBeginTime && !"".equals(strBeginTime))
			{
				hql.append(" and ja.accountTime>=:begintime");
			}

			if (null != strEndTime && !"".equals(strEndTime))
			{
				hql.append(" and ja.accountTime<:endtime");
			}

			Query query = session.createQuery(hql.toString());

			query.setString("stateName", "ACCOUNTTYPE");

			if (StringUtils.isNotBlank(iType.toString()))
			{
				query.setInteger("type", iType);
			}

			if (StringUtils.isNotBlank(iState.toString()))
			{
				query.setInteger("state", iState);
			}

			if (StringUtils.isNotBlank(fstartNum.toString()))
			{
				query.setFloat("startnum", fstartNum);
			}

			if (StringUtils.isNotBlank(fendNum.toString()))
			{
				query.setFloat("endnum", fendNum);
			}

			if (null != strBeginTime && !"".equals(strBeginTime))
			{
				query.setDate("begintime", strBeginTime);
			}

			if (null != strEndTime && !"".equals(strEndTime))
			{
				query.setDate("endtime", strEndTime);
			}

			caccountList = query.list();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return caccountList;
	}

	/**
	 * 账单信息导出查询
	 * 
	 * @param iType
	 * @param iState
	 * @param fstartNum
	 * @param fendNum
	 * @param strBeginTime
	 * @param strEndTime
	 * @return
	 * @throws Exception
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<JomooAccount> getAccountExcel(Integer iType, Integer iState, Float fstartNum,
									Float fendNum, Date strBeginTime, Date strEndTime)
																					throws Exception
	{
		Session session = sessionFactory.openSession();

		List<JomooAccount> caccountList = null;

		StringBuilder hql = new StringBuilder();
		try
		{
			if(0 == iType)
			{
				hql.append(" select new JomooAccount(js.statusName, ja.isBalance, jt.typeName, jsp.specName,ja.accountSale, "
						+ " ja.accountTime, ja.remark) "
						+ " from JomooAccount ja, JoMooStatus js, JoMooType jt, JoMooSpec jsp "
						+ " where ja.active=1 and js.active=1 and jt.active=1 and jsp.active=1 "
						+ " and ja.accountType=js.statusValue"
						+ " and js.stateName =:stateName "
						+ " and ja.typeId = jt.id " 
						+ " and ja.specId = jsp.id ");
			}
			else 
			{
				hql.append(" select new JomooAccount(js.statusName, ja.isBalance, ja.typeId, ja.specId, ja.accountSale, "
						+ " ja.accountTime, ja.remark) "
						+ " from JomooAccount ja, JoMooStatus js "
						+ " where ja.active=1 and js.active=1"
						+ " and ja.accountType=js.statusValue"
						+ " and js.stateName =:stateName and ja.cardId is null ");
			}
			
			if (null != iType && !"".equals(iType))
			{
				hql.append(" and ja.accountType=:type ");
			}

			if (null != iState && !"".equals(iState))
			{
				hql.append(" and ja.isBalance=:state ");
			}

			if (null != fstartNum && !"".equals(fstartNum))
			{
				hql.append(" and ja.accountSale>=:startnum ");
			}

			if (null != fendNum && !"".equals(fendNum))
			{
				hql.append(" and ja.accountSale<=:endnum ");
			}

			if (null != strBeginTime && !"".equals(strBeginTime))
			{
				hql.append(" and ja.accountTime>=:begintime");
			}

			if (null != strEndTime && !"".equals(strEndTime))
			{
				hql.append(" and ja.accountTime<:endtime");
			}

			Query query = session.createQuery(hql.toString());

			query.setString("stateName", "ACCOUNTTYPE");

			if (null != iType && !"".equals(iType))
			{
				query.setInteger("type", iType);
			}

			if (null != iState && !"".equals(iState))
			{
				query.setInteger("state", iState);
			}

			if (null != fstartNum && !"".equals(fstartNum))
			{
				query.setFloat("startnum", fstartNum);
			}

			if (null != fendNum && !"".equals(fendNum))
			{
				query.setFloat("endnum", fendNum);
			}

			if (null != strBeginTime && !"".equals(strBeginTime))
			{
				query.setDate("begintime", strBeginTime);
			}

			if (null != strEndTime && !"".equals(strEndTime))
			{
				query.setDate("endtime", strEndTime);
			}

			caccountList = query.list();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return caccountList;
	}
	
	/**
	 * 作废账目
	 * 
	 * @param iId
	 * @return
	 * @throws Exception
	 */
	@Override
	public Integer deleteAccount(Integer iId) throws Exception
	{
		Integer iCount = null;
		Session session = sessionFactory.openSession();

		try
		{
			StringBuilder strHql = new StringBuilder();
			
			strHql.append(" update JomooAccount ja set ja.active=0 where ja.id=:id ");

			Query query = session.createQuery(strHql.toString());

			query.setInteger("id", iId);

			Number number = query.executeUpdate();

			iCount = number.intValue();

			session.flush();
		}
		catch (Exception e)
		{
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return iCount;
	}

	/**
	 * 获取当月产品各自安装数量用于图表显示
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public List getMonthAccountView(Date startDate, Date endDate)
			throws Exception
	{
		List monthView = null;
		Session session = sessionFactory.openSession();

		try
		{
			StringBuilder strHql = new StringBuilder();
			
			strHql.append(" SELECT count(*),CONCAT(jt.type_name,js.spec_name) "
					+ " FROM t_jomoo_card jc,t_jomoo_spec js,t_jomoo_type jt "
					+ " WHERE jc.active=1 AND jc.setup_type=1 and jc.spec_id=js.id "
					+ " and jc.setup_time>=:starttime and jc.setup_time<:endtime "
					+ " AND jc.type_id=jt.id AND js.type_id=jt.id GROUP BY jt.type_name,js.spec_name");

			Query query = session.createSQLQuery(strHql.toString());
			query.setDate("starttime", startDate);
			query.setDate("endtime", endDate);

			monthView = query.list();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return monthView;
	}

	/**
	 * 按金额数目生成图表
	 * @param iCustomer
	 * @param iSetupType
	 * @param iProduct
	 * @param iSpec
	 * @param iSetupState
	 * @param strBeginTime
	 * @param strEndTime
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings( "rawtypes")
	@Override
	public List getAccountsView(Integer iCheck, Integer iCustomer, Integer iSetupType,
									Integer iProduct, Integer iSpec, Integer iSetupState,
															String strBeginTime, Date strEndTime) throws Exception
	{
		List monthView = null;
		Session session = sessionFactory.openSession();
		SimpleDateFormat simp = new SimpleDateFormat("yyyy-MM-dd");
		
		try
		{
			StringBuilder strHql = new StringBuilder();
			
			strHql.append("select");
			
			if(Constants.CHECKSTATE1.equals(iCheck))
			{
				strHql.append(" count(*),CONCAT(jt.type_name,'(',js.spec_name,')') ");
			}
			if(Constants.CHECKSTATE2.equals(iCheck))
			{
				strHql.append(" sum(jc.sale),CONCAT(jt.type_name,'(',js.spec_name,')') ");
			}
			
			strHql.append(" FROM t_jomoo_card jc,t_jomoo_spec js,t_jomoo_type jt "
					+ " WHERE jc.active=1 AND jc.spec_id=js.id AND jc.type_id=jt.id AND js.type_id=jt.id ");
			
			if(null != iCustomer && !"".equals(iCustomer))
			{
				strHql.append(" and jc.customertype=:type ");
			}
			
			if(null != iSetupType && !"".equals(iSetupType))
			{
				strHql.append(" and jc.setup_type=:setuptype");
			}
			
			if(null != iProduct && !"".equals(iProduct))
			{
				strHql.append(" and jc.type_id=:typeid ");
			}
			
			if(null != iSpec && !"".equals(iSpec))
			{
				strHql.append(" and jc.spec_id=:specid ");
			}
			
			if(null != iSetupState && !"".equals(iSetupState))
			{
				strHql.append(" and jc.setup_state=:setupstate ");
			}
			
			if(null != strBeginTime && !"".equals(strBeginTime))
			{
				strHql.append(" and jc.setup_time>=:starttime ");
			}
			
			if(null != strEndTime && !"".equals(strEndTime))
			{
				strHql.append(" and jc.setup_time<:endtime ");
			}
			
			if(Constants.CHECKSTATE1.equals(iCheck))
			{
				strHql.append(" GROUP BY js.spec_name");
			}
			if(Constants.CHECKSTATE2.equals(iCheck))
			{
				strHql.append(" GROUP BY jc.sale");
			}

			Query query = session.createSQLQuery(strHql.toString());
			
			if(null != iCustomer && !"".equals(iCustomer))
			{
				query.setInteger("type", iCustomer);
			}
			
			if(null != iSetupType && !"".equals(iSetupType))
			{
				query.setInteger("setuptype", iSetupType);
			}
			
			if(null != iProduct && !"".equals(iProduct))
			{
				query.setInteger("typeid", iProduct);
			}
			
			if(null != iSpec && !"".equals(iSpec))
			{
				query.setInteger("specid", iSpec);
			}
			
			if(null != iSetupState && !"".equals(iSetupState))
			{
				query.setInteger("setupstate", iSetupState);
			}
			
			if(null != strBeginTime && !"".equals(strBeginTime))
			{
				query.setDate("starttime", simp.parse(strBeginTime));
			}
			
			if(null != strEndTime && !"".equals(strEndTime))
			{
				query.setDate("endtime", strEndTime);
			}

			monthView = query.list();
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		finally
		{
			if (null != session)
			{
				session.close();
			}
			if (null != sessionFactory)
			{
				sessionFactory.close();
			}
		}
		return monthView;
	}
}